列出各门课程成绩最好的两位学生(要求显示字段: 学号，姓名,科目，成绩)经典面试题

创建表

CREATE TABLE t_student_score( stuid INT(11), NAME VARCHAR(50), SUBJECT VARCHAR(50), score INT(11) )

  INSERT INTO t_student_score VALUES(10001,'张三','语文',89); INSERT INTO t_student_score VALUES(10001,'张三','数学',95); INSERT INTO t_student_score VALUES(10001,'张三','外语',70); INSERT INTO t_student_score VALUES(10002,'李四','语文',95); INSERT INTO t_student_score VALUES(10002,'李四','数学',80); INSERT INTO t_student_score VALUES(10002,'李四','外语',75); INSERT INTO t_student_score VALUES(10003,'王五','语文',85); INSERT INTO t_student_score VALUES(10003,'王五','数学',90); INSERT INTO t_student_score VALUES(10003,'王五','外语',70);

--  列出各门课程成绩最好的两位学生(要求显示字段: 学号，姓名,科目，成绩)

SELECT t1.name,t1.SUBJECT,t1.score FROM t_student_score t1 WHERE ( SELECT COUNT(1) FROM t_student_score t2 WHERE t1.SUBJECT=t2.SUBJECT AND t2.score>=t1.score ) ( SELECT COUNT(t.stuid) FROM t_student_score t WHERE t.SUBJECT = a.SUBJECT AND t.score > a.score ) ORDER BY a.SUBJECT,a.score ;